﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Public;
using System.Data;

namespace DAL
{
    public class AdvertsimentDAL
    {
        DBConnection conn = new DBConnection();
        public DataTable getAllProduct()
        {
            return conn.loadData("SELECT p.productId AS 'Product Id', p.barCode AS 'Barcode', " +
            "(SELECT pn.productName FROM ProductName pn WHERE pn.proNameId = p.proNameId) AS 'Product name', " +
            "(SELECT pt.typeName FROM ProductType pt WHERE pt.typeId = (SELECT pn.typeId FROM ProductName pn " +
            "WHERE pn.proNameId = p.proNameId)) AS 'Product type', " +
            "(SELECT u.unitName FROM Unit u WHERE u.unitId = p.unitId) AS Unit, " +
            "(SELECT b.brandName FROM Brand b WHERE b.brandId = p.brandId) AS Brand, " +
            "CONVERT(VARCHAR(10),p.expiredDate,103) AS 'Expired date', " +
            "p.quantity AS 'Current Quantity', p.unitPrice " +
            "FROM Product p WHERE p.isShow = 1 ");
        }
        public int saveAdvertisement(AdvertedProd advertedP)
        {
            int Nparameter = 4;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@content"; value[0] = advertedP.Advertisement.Content;
            name[1] = "@title"; value[1] = advertedP.Advertisement.Title;
            name[2] = "@excutedFromDate"; value[2] = advertedP.Advertisement.FromDate;
            name[3] = "@excutedToDate"; value[3] = advertedP.Advertisement.ToDate;
            string query = " INSERT INTO Advertisement(content,title,excutedFromDate,excutedToDate) " +
            "VALUES (@content,@title,@excutedFromDate,@excutedToDate) ";
            return conn.Update(query, name, value, Nparameter);
        }

        public int saveAdvertedProducts(AdvertedProd advertedP)
        {
            int Nparameter = 2;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@productId"; value[0] = advertedP.Product.ProductId;
            name[1] = "@saleOff"; value[1] = advertedP.SaleOff;
            string query = "DECLARE @AdvertId int; " +
            "SELECT @AdvertId = (SELECT TOP 1 advertId FROM Advertisement ORDER BY advertId DESC) " +
            "INSERT INTO AdvertedProd(advertId,productId,saleOff) VALUES (@AdvertId,@productId,@saleOff)";
            return conn.Update(query, name, value, Nparameter);
        }

        public DataTable checkTodayFrom(string today)
        {
            return conn.loadData(" SELECT advertId FROM Advertisement WHERE excutedFromDate = " + today);
        }

        public DataTable getAdvertedProduct(int advertId)
        {
            return conn.loadData("SELECT p.worth, p.productId, ad.saleOff FROM " +
            "AdvertedProd ad JOIN Product p ON p.productId = ad.productId " +
            "WHERE ad.advertId = " + advertId);
        }

        public int updateWorth(Product product)
        {
            int Nparameter = 2;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@worth"; value[0] = product.Worth;
            name[1] = "@productId"; value[1] = product.ProductId;
            string query = "UPDATE Product SET worth = @worth WHERE productId = @productId";
            return conn.Update(query, name, value, Nparameter);
        }
    }
}
